package com.infore.water.dao;

import com.infore.water.entity.Factor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class RealDataDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据站点id获取实时数据
     * @param stationId
     * @return
     */
    public List<Map<String,Object>> queryRealDataByStationId(String stationId){
        String sql = "select a.station_id stationId,a.project_id projectId,a.proj_name projectName,c.name stationName,a.value,a.state,b.unit,b.cname projectCname,CONVERT(varchar,a.recTime,20) receiveTime,CONVERT(varchar,a.sendTime,20) sendTime from T_RealData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id  where a.station_id=?";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql,stationId);
    }

    public List<Map<String,Object>> queryHistoryHourData(String stationId,String startTime,String endTime){
        String sql = "select a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg value,b.unit,b.cname from T_EnvHourData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id where a.station_id = ? and a.project_id in (select project_id from T_Mid_ProjectStation where station_id=?) and CONVERT(VARCHAR,a.time) BETWEEN ? and ? order by a.time";
        return jdbcTemplate.queryForList(sql,stationId,stationId,startTime,endTime);
    }

    public List<Map<String,Object>> queryHistoryDayData(String stationId,String startTime,String endTime){
        String sql = "select a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg value,b.unit,b.cname from T_EnvDayData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id where a.station_id = ? and a.project_id in (select project_id from T_Mid_ProjectStation where station_id=?) and CONVERT(VARCHAR,a.time) BETWEEN ? and ? order by a.time";
        return jdbcTemplate.queryForList(sql,stationId,stationId,startTime,endTime);
    }

    public List<Map<String,Object>> queryHistoryMonthData(String stationId,String startTime,String endTime){
        String sql = "select a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg value,b.unit,b.cname from T_EnvMonthData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id  where a.station_id = ?  and a.project_id in (select project_id from T_Mid_ProjectStation where station_id=?) and CONVERT(VARCHAR,a.time) BETWEEN ? and ? order by a.time";
        return jdbcTemplate.queryForList(sql,stationId,stationId,startTime,endTime);
    }

    public List<Factor> queryFactorByStationId(String stationId){
        String sql = "select a.project_id projectId,b.name projectName,b.cname projectCname,b.unit  from T_Mid_ProjectStation a left join T_EnvProject b on a.project_id=b.id where a.station_id="+stationId;
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(Factor.class));
    }
}
